package shopping.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import shopping.dao.CategoryDao;
import shopping.domain.Category;
import shopping.utils.DataSourceUtil;

import java.sql.SQLException;
import java.util.List;

public class CategoryDaoMysql implements CategoryDao {
    public List<Category> findAll() {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from category";

        try {
            return qr.query(sql, new BeanListHandler<>(Category.class));
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public Category findById(String cid) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from category where cid = ?";

        try {
            return qr.query(sql,new BeanHandler<>(Category.class),cid);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public void save(Category category) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "insert into category values(?,?)";

        try {
            qr.update(sql,category.getCid(),category.getCname());
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public void update(Category category) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "update category set cname = ? where cid = ?";

        try {
            qr.update(sql,category.getCname(),category.getCid());
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public void del(String cid) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "delete from category where cid = ?";

        try {
            qr.update(sql, cid);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public int countByCid(String cid) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select count(*) from product where cid = ?";

        try {
            return ((Long)(qr.query(sql, new ScalarHandler(),cid))).intValue();
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }
}
